package com.fsd.admin.service.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;
import javax.annotation.Resource;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;
import com.fsd.admin.dao.Sys_DatabackupDao;
import com.fsd.admin.model.A_Employee;
import com.fsd.admin.model.Sys_Databackup;
import com.fsd.admin.service.Sys_DatabackupService;
import com.fsd.core.util.ParametersUtil;
import com.google.gson.Gson;

/**
 * 数据备份Service-实现类
 * @author Administrator
 *
 */
@Service("sys_databackupServiceImpl")
public class Sys_DatabackupServiceImpl extends MainServiceImpl<Sys_Databackup, String> implements Sys_DatabackupService {

	@Resource(name = "sys_databackupDaoImpl")
	private Sys_DatabackupDao databackupDao;

	/**
	 * 删除对象
	 * @param parameters
	 * @throws Exception
	 */
	public void delObject(ParametersUtil parameters) throws Exception{
		Gson gs = new Gson();
		Map objectMap = gs.fromJson(parameters.getJsonData(), Map.class);
		ArrayList<String> dir = (ArrayList<String>) objectMap.get("ids");
		String id = this.getSQLinByIDList(dir);
		databackupDao.executeHql("delete from Sys_Databackup where id = " + id + "");
	}
	
	/**
	 * 加载备份数据（分页）
	 * @param param
	 * @return
	 * @throws Exception
	 */
	public ParametersUtil getDataPageList(ParametersUtil param) throws Exception{
		Criteria criteria = databackupDao.createCriteria();
		criteria.add(Restrictions.eq("deleted", "0"));
		criteria.addOrder(Order.desc("backupdate"));
		if(param.getJsonData() != null && !"".equals(param.getJsonData())){
			Gson gs = new Gson();
			Map objectMap = gs.fromJson(param.getJsonData(), Map.class);
			if(objectMap.get("name") != null && !"".equals(objectMap.get("name"))) {
				criteria.add(Restrictions.like("filename", "%"+ objectMap.get("name") +"%"));
			}
		}
		return databackupDao.findPager(param, criteria);
	}
	
	/**
	 * 根据Id加载对象
	 * @param parameters
	 * @return
	 * @throws Exception
	 */
	public Sys_Databackup getObjectById(ParametersUtil parameters) throws Exception{
		Gson gs = new Gson();
		Map objectMap = gs.fromJson(parameters.getJsonData(), Map.class);
		String id = (String) objectMap.get("id");
		Sys_Databackup databackup = databackupDao.get(id);
		File file = new File(databackup.getFilepath()+"/"+databackup.getFilename());
		String filesize = String.valueOf(file.length()/1024);
		databackup.setFilesize(filesize+"KB");
		return databackup;
	}
	
	/**
	 * 数据备份操作
	 * @param parameters
	 * @throws Exception
	 */
	public void saveDatabackup(A_Employee employee) throws Exception{
		DataBackup dataBackup = new DataBackup(); 
		Sys_Databackup backup = new Sys_Databackup();
    	Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        
        //oracle数据库(参数：userName, password, SID, savePath, fileName)
        //dataBackup.exportDatabaseTool_Oracle("bdqn", "bdqn", "127.0.0.1/jbit", "d:/databackup", sdf.format(date)); 
        
        // MySql数据库(参数：hostIp, userName, password, savePath, fileName, databaseName)
        dataBackup.exportDatabaseTool_Mysql("127.0.0.1", "root", "liyouhuan", "d:/databackup", sdf.format(date)+".sql", "bdqn");
        
        // sqlServer数据库(参数：URL, userName, password, filePath, fileName, databaseName)
        // dataBackup.exportDatabaseTool_Sqlserver("jdbc:sqlserver://127.0.0.1:1433;databasename=Lzyz", "sa", "Fsd101202303", "d:/databackup", sdf.format(date) +".bak", "Lzyz");
        
        backup.setId(this.getUUID());
        backup.setBackupdate(sdf.format(date));
        if(sdf.format(date).substring(8, 10).equals("04")) {
            backup.setBackupemployeename("系统自动备份");
        }else {
        	backup.setBackupemployeeid(employee.getId());
            backup.setBackupemployeename(employee.getRealname());
        }
        backup.setDeleted("0");
        // backup.setFilename(sdf.format(date) + ".dmp"); // oracle备份文件名称
        backup.setFilename(sdf.format(date) + ".sql"); // mySql备份文件名称
        // backup.setFilename(sdf.format(date) + ".bak"); // sqlServer备份文件名称
        backup.setFilepath("d:/databackup"); //备份文件路径
        databackupDao.save(backup);
	}
	public class DataBackup  {
		// oracle数据库
	    public boolean exportDatabaseTool_Oracle(String userName, String password, String SID, String savePath, String fileName)throws InterruptedException {  
	        File saveFile = new File(savePath);  
	        if (!saveFile.exists()) {
	            saveFile.mkdirs();  
	        }  
	        try {  
	            String str = "exp " + userName + "/" + password + "@" + SID + " file='" + savePath + "/" + fileName + ".dmp' ";  
	            Process process = null;  
	            process = java.lang.Runtime.getRuntime().exec("cmd  /c  start cmd.exe /c " + str);	
	            if (process.waitFor() == 0) {  
	                return true;              
	            }    
	        } catch (IOException e) {  
	            e.printStackTrace();  
	        }  
	        return false;  
	    }  
	    
	    // MySql数据库
		public boolean exportDatabaseTool_Mysql(String hostIP, String userName, String password, String savePath, String fileName, String databaseName) throws InterruptedException {  
			File saveFile = new File(savePath);  
			if (!saveFile.exists()) { 
				saveFile.mkdirs();
			}  
			if(!savePath.endsWith(File.separator)){  
				savePath = savePath + File.separator;  
			}  
			PrintWriter printWriter = null;  
			BufferedReader bufferedReader = null;  
			try {  
				printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8"));  
				Process process = Runtime.getRuntime().exec(" mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName);  
				InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");  
				bufferedReader = new BufferedReader(inputStreamReader);  
				String line;  
				while((line = bufferedReader.readLine()) != null){  
					printWriter.println(line);  
				}  
				printWriter.flush();  
				if(process.waitFor() == 0){  
					return true;  
				}  
			}catch (IOException e) {  
				e.printStackTrace();  
			} finally {  
				try {  
					if (bufferedReader != null) {  
						bufferedReader.close();  
					}  
					if (printWriter != null) {  
						printWriter.close();  
					}  
				} catch (IOException e) {  
					e.printStackTrace();  
				}  
			}  
	    	return false;  
		}  		

		// sqlServer数据库
		public boolean exportDatabaseTool_Sqlserver(String url, String userName, String password, String filePath, String fileName, String databaseName) throws Exception {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		    Connection connection = DriverManager.getConnection(url, userName, password);
		    File saveFile = new File(filePath); 
		    String path = filePath + "/" + fileName;
		    if (!saveFile.exists()) { 
				saveFile.mkdirs();
			}  
	        String dataBackupSql = "backup database " + databaseName + " to disk = '" + path + "' with init";  
		    PreparedStatement prepareStatement = null;  
	        try {  
	            prepareStatement = connection.prepareStatement(dataBackupSql);
	            prepareStatement.execute();
	            return true;  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        } finally {  
	            try {  
	                prepareStatement.close();  
	                connection.close();
	            } catch (Exception e1) {  
	                e1.printStackTrace();  
	            }  
	        }  
	        return false;  
		}
	}
	
	/**
	 * 恢复数据(数据还原)
	 * @param parameters
	 * @throws Exception
	 */
	public void recoveryData(ParametersUtil parameters) throws Exception{
		Gson gs = new Gson();
		Map objectMap = gs.fromJson(parameters.getJsonData(), Map.class);
		String id = (String) objectMap.get("id");
		Sys_Databackup databackup = databackupDao.get(id);
		String filepath = databackup.getFilepath();
		String filename = databackup.getFilename();
		DataRecovery recovery = new DataRecovery();
		
		// mySql数据库(参数：mysqlPath, hostIp, userName, password, filePath, filename, databaseName)
		recovery.loadData_mysql("D:\\MySQL\\bin\\", "127.0.0.1", "root", "liyouhuan", filepath, filename, "bdqn");
		
		// oracle数据库(参数：userName, password, SID, filePath, filename)
		// recovery.loadData_oracle("bdqn", "bdqn", "127.0.0.1/jbit", filepath, filename);
		
		// sqlServer数据库
		// recovery.loadData_sqlserver("jdbc:sqlserver://127.0.0.1:1433;databasename=Lzyz", "sa", "Fsd101202303", filepath, filename, "Lzyz");
	}
	public class DataRecovery {
		// mySql数据库
		public void loadData_mysql(String mysqlPath,String hostIp, String username, String password, String filepath, String fileName, String databasename){  
            try {
    			Runtime runtime = Runtime.getRuntime();
            	Process process = runtime.exec(mysqlPath + "mysql.exe -h" + hostIp + " -u" + username+" -p" + password + " --default-character-set=utf8 " + databasename + "");
				OutputStream outputStream = process.getOutputStream();
				BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(filepath+"/"+fileName), "utf-8"));
	            String str = null;
	            StringBuffer sb = new StringBuffer();
	            while ((str = br.readLine()) != null) {
	                sb.append(str + "\r\n");
	            }
	            str = sb.toString();
	            OutputStreamWriter writer = new OutputStreamWriter(outputStream,"utf-8");
	            writer.write(str);
	            writer.flush();
	            writer.close();
	            br.close();
	            outputStream.close();
            } catch (Exception e) {
				e.printStackTrace();
			}
	    }
		
		// oracle数据库
		public boolean loadData_oracle(String username, String password, String SID,String filepath, String fileName){
			try {  
	            Process process = Runtime.getRuntime().exec("imp " + username + "/" + password + "@" + SID + " ignore=y file=" + filepath + "/" + fileName);  
	            if(process.waitFor() == 0){
	                return true;  
	            }  
	        } catch (Exception e) {  
	            e.printStackTrace();  
	        }  
	        return false; 
		}
		
		// sqlServer数据库
		public boolean loadData_sqlserver(String url, String userName, String password, String filePath, String fileName, String databaseName) throws Exception {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		    Connection connection = DriverManager.getConnection(url, userName, password);
		    String path = filePath + "/" + fileName;
		    String recoverySql = "ALTER DATABASE " + databaseName + " SET ONLINE WITH ROLLBACK IMMEDIATE";
		    PreparedStatement prepareStatement = null; 
            CallableStatement callableStatement = null;
		    try {
		    	prepareStatement = connection.prepareStatement(recoverySql);
		    	callableStatement = connection.prepareCall("{call killrestore(?,?)}");
		    	callableStatement.setString(1, databaseName);
		    	callableStatement.setString(2, path);
		    	callableStatement.execute();
		    	prepareStatement.execute();
		    	return true;
		    } catch (Exception e) {  
	            e.printStackTrace();
		    } finally {
		    	callableStatement.close();
		    	prepareStatement.close();
		    	connection.close();
		    }
		    return false;
		}
	}
}